package com.thirdgroup.dao.impl;

import com.thirdgroup.dao.CustomerPhoneTotalDao;
import com.thirdgroup.dao.base.BaseDAO;
import com.thirdgroup.pojo.vo.CustomerPhoneTotalVo;

import java.util.List;

public class CustomerPhoneToalDaoImpl extends BaseDAO<CustomerPhoneTotalVo> implements CustomerPhoneTotalDao {
    @Override
    public List<CustomerPhoneTotalVo> customerToal() {
        String sql="SELECT customSum.customerID, SUM(customSum.total) AS total\n" +
                "FROM \n" +
                "    (SELECT us.customerID, sum.total\n" +
                "    FROM user_info us\n" +
                "    INNER JOIN\n" +
                "        (SELECT u.userPhone, SUM(bill.callDuration) AS duration, SUM(bill.phoneCharge) AS total\n" +
                "        FROM user_info u\n" +
                "        INNER JOIN bill_info bill ON u.userPhone = bill.callingPhone\n" +
                "        WHERE bill.createTime >= DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)\n" +
                "          AND bill.createTime < DATE_FORMAT(NOW(), '%Y-%m-01')\n" +
                "        GROUP BY u.userPhone) AS sum\n" +
                "    ON us.userPhone = sum.userPhone) AS customSum\n" +
                "GROUP BY customSum.customerID;";
        return super.findAll(sql,null);
    }
}
